 /*
 select * from fn_all_locations_yield() where lotnumber = 'TTXHL.01' order by lotnumber, processStepid
 */
 
 ALTER   function fn_all_locations_yield()
 
 returns  @fn_result Table( ProcessStepName varchar(20), ProcessStepID int, lot_id int, LotNumber varchar(20), ProductName varchar(30), DateIn datetime, DateOut datetime, QtyIn int, 
 			  QtyOut int, Yield float, GrossDie int, Stage int, VendoLoc varchar(20))
 as
 
 begin
 /*variable to store the values
 */
 	declare @qty_in int, @qty_out int,  @lot_id int, @die_wafer int
 	declare @lot_no varchar(20), @prod_name varchar(20), @location varchar(30)
 	declare @yield float
 	declare @date_in datetime, @date_out datetime
 	declare @step_id int
 	declare @gross_die int--Bhaskar added this on 06 Feb 2004
 	
 	declare @results Table( ProcessStepName varchar(20), processStepID int, lot_id int, LotNumber varchar(20), ProductName varchar(30), DateIn datetime, DateOut datetime, QtyIn int, QtyOut int, Yield float, GrossDie int, Stage int, VendorLoc varchar(30))
 	
 	declare fabLotCursor cursor for
 		--Bhaskar modified this query and cursor on 06 Feb 2004
 		select Fab_Lot_History.Lot_ID, Lot_Number, FL_Date_In, FL_Date_Out, FL_Input_Quantity, FL_Output_Quantity, FL_Yield, FL_GROSS_DIE_WAFER from Fab_Lot_History, Lot_Table where Fab_Lot_History.Lot_Id = Lot_Table.Lot_Id and FL_Current_Status = 'close'
 		open fabLotCursor
 		fetch next from fabLotCursor into @lot_id, @lot_no, @date_in, @date_out, @qty_in, @qty_out, @yield, @gross_die
 		while @@fetch_status = 0
 		begin
 			select @step_id = ProcessStep_ID from lot_table where lot_id = @lot_id
 			select @prod_name =part_Number from product where processStep_ID = @step_id
 			select @location = (select L_Vendor_Name from Location,Lot_Table where Lot_id=@lot_id and lot_table.location_Id = location.location_Id)
 			insert into @results values( 'FAB', @step_id, @lot_id, @lot_no, @prod_name, @date_in, @date_out, @qty_in, @qty_out, @yield, @gross_die,1, @location)
 			set @date_in =null
 			set @date_out=null
 			set  @qty_in=null
 			set  @qty_out=null
 			set  @yield=null
 			set  @gross_die = null	--Bhaskar added this on 06 Feb 2004
 		
 		fetch next from fabLotCursor into @lot_id, @lot_no, @date_in, @date_out, @qty_in, @qty_out, @yield, @gross_die
 		end
 		close fabLotCursor
 		deallocate fabLotCursor
 	--select * from sort_lot_history
 	declare sortLotCursor cursor for
 		select Lot_ID, SL_Date_In, SL_Date_Out, SL_Wafer_Qty, SL_Die_Qty_Out, SL_Yield, SL_Die_Wafer from Sort_Lot_History where SL_Current_Status = 'close'
 		open sortLotCursor
 		fetch next from sortLotCursor into @lot_id, @date_in, @date_out, @qty_in, @qty_out, @yield, @die_wafer
 		while @@fetch_status = 0
 		begin
 			select @lot_no=Lot_Number, @step_id = ProcessStep_ID from lot_table where lot_ID = @lot_id
 			if @qty_in is null
 				select @qty_in  = SL_Qty_Out from sort_lot_history where lot_id =@lot_id
 			select @location = (select L_Vendor_Name from Location,Lot_Table where Lot_id=@lot_id and lot_table.location_Id = location.location_Id)
 			select @prod_name =part_Number from product where processStep_ID = @step_id
 			insert into @results values( 'SORT', @step_id, @lot_id, @lot_no, @prod_name, @date_in, @date_out, @qty_in, @qty_out, @yield, @die_wafer, 2,@location)
 			set @date_in =null
 			set @date_out=null
 			set  @qty_in=null
 			set  @qty_out=null
 			set  @yield=null		
 		
 		fetch next from sortLotCursor into @lot_id, @date_in, @date_out, @qty_in, @qty_out, @yield,@die_wafer
 		end
 		close sortLotCursor
 		deallocate sortLotCursor
 	--select * from Assembly_lot_history
 	declare assemblyLotCursor cursor for
 		select Lot_ID, AL_Date_In, AL_Date_Out, AL_Qty_In, AL_Qty_Out, AL_Asy_Yield from Assembly_Lot_History where AL_Current_Status = 'close'
 		open assemblyLotCursor
 		fetch next from assemblyLotCursor into @lot_id, @date_in, @date_out, @qty_in, @qty_out, @yield
 		while @@fetch_status = 0
 		begin
 			select @lot_no=Lot_Number, @step_id = ProcessStep_ID from lot_table where lot_ID = @lot_id
 			select @prod_name =part_Number from product where processStep_ID = @step_id
 			select @location = (select L_Vendor_Name from Location,Lot_Table where Lot_id=@lot_id and lot_table.location_Id = location.location_Id)
 			insert into @results values( 'ASSEMBLY', @step_id, @lot_id, @lot_no, @prod_name, @date_in, @date_out, @qty_in, @qty_out, @yield, null, 3,@location)
 			set @date_in =null
 			set @date_out=null
 			set  @qty_in=null
 			set  @qty_out=null
 			set  @yield=null		
 		
 		fetch next from assemblyLotCursor into @lot_id, @date_in, @date_out, @qty_in, @qty_out, @yield
 		end
 		close assemblyLotCursor
 		deallocate assemblyLotCursor
 	
 	declare testLotCursor cursor for
 		select Lot_ID, FTL_Date_In, FTL_Date_Out, FTL_Qty_In, FTL_Qty_Out, FTL_Yield from Final_Test_Lot_History where FTL_Current_Status = 'close'
 		open testLotCursor
 		fetch next from testLotCursor into @lot_id, @date_in, @date_out, @qty_in, @qty_out, @yield
 		while @@fetch_status = 0
 		begin
 			select @lot_no=Lot_Number, @step_id = ProcessStep_ID from lot_table where lot_ID = @lot_id
 			select @prod_name =part_Number from product where processStep_ID = @step_id
 			select @location = (select L_Vendor_Name from Location,Lot_Table where Lot_id=@lot_id and lot_table.location_Id = location.location_Id)
 			insert into @results values( 'TEST', @step_id, @lot_id, @lot_no, @prod_name, @date_in, @date_out, @qty_in, @qty_out, @yield, null, 4,@location)
 			set @date_in =null
 			set @date_out=null
 			set  @qty_in=null
 			set  @qty_out=null
 			set  @yield=null		
 		
 		fetch next from testLotCursor into @lot_id, @date_in, @date_out, @qty_in, @qty_out, @yield
 		end
 		close testLotCursor
 		deallocate testLotCursor
 	-- Added by Prabhanjan on 12-Dec-2003 to show inventory lot details in the yield report.
 	declare inventoryLotCursor cursor for
 		select Lot_ID, IL_Date_In, IL_Date_Out, IL_Qty_In, IL_Qty_Out from Inventory_Lot_History where IL_Current_Status = 'close' and Lot_Id not in ( select lot_id from lot_table where processstep_id in(select processstep_id from process_step where PS_Type_Name = 'DIE BANK') )
 		open inventoryLotCursor
 		fetch next from inventoryLotCursor into @lot_id, @date_in, @date_out, @qty_in, @qty_out
 		set @yield  = null
 		declare @stage int
 		declare @ps_type_name varchar(50)
 		SET @stage = null
 
 
 		while @@fetch_status = 0
 		begin
 			select @lot_no=Lot_Number, @step_id = ProcessStep_ID from lot_table where lot_id = @lot_id
 			select @ps_type_name = PS_Type_Name from process_step where processStep_ID = @step_id
 			if @ps_type_name = 'WAFER STORE' 
 				set @stage = 5
 			if @ps_type_name = 'CLASS STORE' 
 				set @stage = 7
 			if @ps_type_name = 'FINISHED GOODS' 
 				set @stage = 8
 
 
 			select @prod_name =part_Number from product where processStep_ID = @step_id
 			select @location = (select L_Vendor_Name from Location,Lot_Table where Lot_id=@lot_id and lot_table.location_Id = location.location_Id)
 			insert into @results values(@ps_type_name, @step_id, @lot_id, @lot_no, @prod_name, @date_in, @date_out, @qty_in, @qty_out, @yield, null,@stage, @location)
 			set @date_in =null
 			set @date_out=null
 			set  @qty_in=null
 			set  @qty_out=null
 			set  @yield=null		
 		
 		fetch next from inventoryLotCursor into @lot_id, @date_in, @date_out, @qty_in, @qty_out
 		end
 		close inventoryLotCursor
 		deallocate inventoryLotCursor
 	
 	declare dieBankLotCursor cursor for
 		select Lot_ID, IL_Date_In, IL_Date_Out, IL_Qty_In, IL_Qty_Out, IL_Current_Status  from Inventory_Lot_History where Lot_Id in ( select lot_id from lot_table where processstep_id in(select processstep_id from process_step where PS_Type_Name = 'DIE BANK') ) order by Lot_ID, TimeStamp
 		open dieBankLotCursor
 		
 		declare @il_current_status varchar(20)
 		declare @first_date_in datetime
 		declare @first_qty_in int
 		declare @first_lot_id int
 		fetch next from dieBankLotCursor into @lot_id, @date_in, @date_out, @qty_in, @qty_out, @il_current_status
 		set @first_lot_id = @lot_id
 		set @first_date_in = @date_in
 		set @first_qty_in = @qty_in
 		set @yield  = null
 
 		while @@fetch_status = 0
 		begin
 			select @lot_no=Lot_Number, @step_id = ProcessStep_ID from lot_table where lot_id = @lot_id
 			select @prod_name =part_Number from product where processStep_ID = @step_id
 			select @location = (select L_Vendor_Name from Location,Lot_Table where Lot_id=@lot_id and lot_table.location_Id = location.location_Id)
 						
 			if @il_current_status = 'Close'
 			begin
 				insert into @results values('DIE BANK', @step_id, @lot_id, @lot_no, @prod_name, @first_date_in, @date_out, @first_qty_in, @first_qty_in, @yield, null, 6, @location)
 			end
 
 			if @first_lot_id <> @lot_id
 			begin
 				set @first_date_in = @date_in
 				set @first_qty_in = @qty_in
 			end
 
 			set @date_in =null
 			set @date_out=null
 			set  @qty_in=null
 			set  @qty_out=null
 			set  @yield=null		
 			set @il_current_status = null	
 
 			
 		fetch next from dieBankLotCursor into @lot_id, @date_in, @date_out, @qty_in, @qty_out, @il_current_status
 		end
 		close dieBankLotCursor
 		deallocate dieBankLotCursor
 		-- till here prabhanjan on 12-Dec-2003
 		
 		insert @fn_result select * from @results 
 return
 end